create or replace package pk_crypto_trade is

    -- Author  : RELAXIMUS
  -- Created : 06.12.2013 23:03:19
  -- Purpose : crypto-trade market adopter
  -- authenication data
  g_api_secret varchar2(100) := '87b79215b3e63be77da4f8e1484dddbba5c1bc19';
  g_api_key    varchar2(100) := '0ACB416E-464D-9121-DA2E-09BBC2AE2395';

  -- book retriever
  function book(a_code_from varchar2, a_code_to varchar2) return pk_tool.TBookStrArr pipelined;
  -- balance retriever
  function balance return pk_tool.TAmountsArr pipelined;
  -- making orders
  function make_order(a_buysell varchar2, a_cur_from varchar2, a_cur_to varchar2, a_rate number, a_amount number) return number;
  -- cancelation of order
  function cancel_order(a_id number) return varchar2;
  -- order info
--  function get_status_order(a_id number) return varchar2;

end pk_crypto_trade;
/
create or replace package body pk_crypto_trade is

  procedure check_freq 
  is
  begin
     pk_tool.check_freq('crypto_trade');
  end;
  
  procedure check_status(a_res varchar2) 
  is
     j json;
  begin
     j := json(a_res);
     if j.get('status').get_string = 'error' then
        raise_application_error(-20001,j.get('error').get_string);
     end if;
  end;
  
  function query_auth(a_link varchar2, a_post_parametrs varchar2 default null) return varchar2
  is
      l_handle  varchar2(200);
      l_nonce   varchar2(50) := pk_lock.lock_and_get_seq('seq_crypto_tr_api_nonce',l_handle);
      l_req     varchar2(1000);
      l_signature varchar2(2000);
      la_headers  pk_http.THeaders := pk_http.g_dummy_headers;
  begin
      check_freq;
      l_req :='nonce='||l_nonce;
      if a_post_parametrs is not null then
        l_req := a_post_parametrs ||'&'|| l_req;
      end if;
      
--      p('link:'||a_link);
--      p('req:'||l_req);
      
      -- generating signature 
      l_signature := pk_crypto.hmac(l_req,pk_crypto_trade.g_api_secret,pk_crypto.g_hmac_sha512);
      -- headers
--      la_headers('Content-Type'):= 'text/xml; charset=utf-8';
      la_headers('Content-type'):= 'application/x-www-form-urlencoded';
      la_headers('Content-Length'):= LENGTH(l_req);
      la_headers('User-Agent'):= 'Mozilla/18.0';
      la_headers('AuthKey'):=pk_crypto_trade.g_api_key;
      la_headers('AuthSign'):= l_signature;
      
      return
      pk_lock.release_seq(l_handle,
         pk_http.request(a_adress => a_link ,a_msg => l_req, aa_headers => la_headers,a_method => 'POST')
      );  
  end;
  
  function book(a_code_from varchar2, a_code_to varchar2) return pk_tool.TBookStrArr pipelined
  is
       ret      json;
       jbs      json_list;
       j        json_list;
       str      pk_tool.TBookStr;
       --
       l_cnt    number;
  begin
    check_freq ;
    -- how much deals per side to return
    select max(p.param_value) into l_cnt from t_system_params p where p.param_code = 'book cnt';
    
    -- if this is pair ltc - btc
    if a_code_to = 'ltc' and a_code_from = 'btc' then
        ret := json(pk_http.request('https://crypto-trade.com/api/1/depth/ltc_btc'));
        ret.print;
       -- buys
       jbs := json_list(ret.get('bids'));
       if jbs.count > 0 then
         for i in 1..least(jbs.count,nvl(l_cnt,jbs.count)) loop
           j := json_list(jbs.get(i));
           str.buysell:='buy';
           str.rate:=j.get(1).get_string;
           str.amount:=j.get(2).get_string;
              pipe row (str);
         end loop;
       end if;
        -- sells
       jbs := json_list(ret.get('asks'));
       if jbs.count > 0 then
         for i in 1..least(jbs.count,nvl(l_cnt,jbs.count)) loop
           j := json_list(jbs.get(i));
           str.buysell:='sell';
           str.rate:=j.get(1).get_string;
           str.amount:=j.get(2).get_string;
              pipe row (str);
         end loop;
       end if;
    end if;
    return;
  end;
  
  function balance return pk_tool.TAmountsArr pipelined
  is

      l_req     varchar2(2000);
      l_res       varchar2(32000);
      --
      l_jres      json;
      l_j         json;
      l_j2        json;
      l_keys      json_list;
      j           json;
      --
      la_bal      pk_tool.TAmounts;
    begin

      l_res:=    
      query_auth(a_link => 'https://crypto-trade.com/api/1/private/getinfo');
--      p(        l_res    );    
      -- parsing of result
      l_jres := json(l_res);
      
      l_j2 := json(l_jres.get('data'));
      l_j  := json(l_j2.get('funds'));
      
      l_keys := l_j.get_keys;
      for i in 1..l_keys.count loop
        la_bal.cur := l_keys.get(i).get_string;
        la_bal.amount := to_number(l_j.get(la_bal.cur).get_string);
        la_bal.cur := lower(la_bal.cur);
        
        if la_bal.amount > 0 then
           pipe row(la_bal);
        end if;
      end loop;
    end;       
    
    function make_order(a_buysell varchar2, a_cur_from varchar2, a_cur_to varchar2, a_rate number, a_amount number) return number
    is 
      l_post_string  varchar2(200):='pair='||a_cur_to||'_'||a_cur_from||'&type='||a_buysell||'&rate='||a_rate||'&amount='||a_amount;
      l_res         varchar2(32000);
        --
      l_jres      json;
      l_j         json;
      l_jv        json_value;
    begin
       l_res := query_auth(a_link => 'https://crypto-trade.com/api/1/private/trade', a_post_parametrs => l_post_string);
       check_status(l_res);
--        p(l_res);
       l_jres :=json(l_res);
       l_j := json(l_jres.get('data').to_char);
       l_jv := l_j.get('order_id');
       
       return l_jv.get_string;      
    end;
    
    function cancel_order(a_id number) return varchar2
    is
      l_post_string  varchar2(200):='orderid='||a_id;
      l_res         varchar2(32000);
        --
      l_jres      json;
      l_j         json;
      l_jv        json_value;
    begin
       l_res := query_auth(a_link => 'https://crypto-trade.com/api/1/private/cancelorder', a_post_parametrs => l_post_string);
       check_status(l_res);
      
       return 'ok';      
    end;    
  
end pk_crypto_trade;
/
